#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ColRecSys - PyGTK Collection Record System
# Copyright (C) 2009 Bertrand Kintanar <b3rxkintanar@gmail.com>
# http://colrecsys.googlecode.com/
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Library General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
#
# If you find any bugs or have any suggestions email: b3rxkintanar@gmail.com
# URL: http://colrecsys.googlecode.com/
#
# ColRecSys needs the following to function properly:
#   python >= 2.5.1, gtk >= 2.12.9, pygtk >= 2.12.2,
#   sqlite >= 2.3.5, sqlalchemy >= 0.5.0

from config import *

import base64
import datetime
import sys

# Try to import sqlalchemy modules
try:

    from sqlalchemy import *
    from sqlalchemy import __version__ as ver
    from sqlalchemy.orm import *
    from sqlalchemy.orm.exc import *
    from sqlalchemy.ext.associationproxy import AssociationProxy
    from sqlalchemy.ext.declarative import declarative_base

    try:

        assert ver >= "0.5.0"

        if DEBUG:

            print "Checking sqlalchemy 0.5.0...... Found"

    except AssertionError:

        print "%s requires SQLAlchemy 0.5.0 or higher, but %s was found" \
            % (APP_NAME, ver)

        sys.exit(1)

except ImportError, e:

    print "Import error POSvsSys cannot start:", e

    sys.exit(1)

Base = declarative_base()

class Log(Base):
    
    __tablename__ = "logs"
    
    id            = Column(Integer, primary_key=True)
    date_time     = Column(String(26))
    description   = Column(String(200))
    user_id       = Column(Integer, ForeignKey('users.id'))
    
    # One2Many
    user    = relation('User', backref=backref('users', order_by=id))
    
    def __init__(self, date_time, description):

        self.date_time   = date_time
        self.description = description

    def __repr__(self):
        
        return "<Log('%s', '%s')>" % (self.date_time, self.description)

class User(Base):

    __tablename__ = "users"

    id            = Column(Integer, primary_key=True)
    username      = Column(String(30))
    password      = Column(String(30))
    permissions   = Column(String(200))
    
    log = relation("Log", order_by="Log.id", backref="logs")
    
    def set_permissions(self, permission_dict):
        
        self.permissions = str(permission_dict)
        
    def get_permissions(self):
        
        return eval(self.permissions)
    
    def __init__(self, username, password):

        self.username = username
        self.password = base64.b64encode(password)

    def __repr__(self):
        
        return "<User('%s', '%s', %s)>" \
            % (self.username, self.password, self.permissions)
    
class TransactionItem(Base):

    __tablename__ = 'transaction_items'

    transaction_id  = Column(Integer, ForeignKey('transactions.id'), primary_key=True)
    item_id         = Column(Integer, ForeignKey('items.id'), primary_key=True)

    item        = relation('Item', lazy=False)
    transaction = relation('Transaction', lazy=False)

    def __init__(self, item):

        self.item = item

    def __repr__(self):

        return "<TransactionItem(%s, %s)>" % (self.transaction_id, self.item_id)
    
class Particular(Base):

    __tablename__ = 'particulars'

    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    description   = Column(String(100))
    cost          = Column(Integer)

    def __init__ (self, name, description, cost):

        self.name        = name
        self.description = description
        self.cost        = cost

    def __repr__(self):

        return "<Particular('%s', '%s', %s)>" % \
               (self.name, self.description, self.cost)

class Item(Base):
    
    __tablename__ = 'items'
    
    id            = Column(Integer, primary_key=True)
    name          = Column(String(100))
    amount        = Column(Float)
    
    # AssociationTables
    transassoc = relation('TransactionItem', cascade="all, delete-orphan", lazy=False)
    
    # Many2Many
    transactions = AssociationProxy('transassoc', 'transaction', creator='TransactionItem')
    
    def __init__(self, name, amount):
        
        self.name   = name
        self.amount = amount
        
    def __repr__(self):
        
        return "<Item('%s', %s)>" % (self.name, self.amount)
    
class Transaction(Base):

    __tablename__ = 'transactions'

    id            = Column(Integer, primary_key=True)
    or_number     = Column(String(10))
    date          = Column(String(10))
    payor         = Column(String(100))
    nature        = Column(String(100))
    t_type        = Column(Integer)
    amount        = Column(Integer)
    
    # AssociationTables
    itemassoc = relation('TransactionItem', cascade="all, delete-orphan", lazy=False)
    
    # Many2Many
    items = AssociationProxy('itemassoc', 'item', creator=TransactionItem)

    def __init__(self, or_number, date, payor, nature, t_type, amount):

        self.or_number = or_number
        self.date      = date
        self.payor     = payor
        self.nature    = nature
        self.t_type    = t_type
        self.amount    = amount

    def __repr__(self):

        return "<Transaction(%s, '%s', '%s', '%s', %s, %s)>" % \
               (self.or_number, self.date, self.payor, self.nature, self.t_type, self.amount)

def checkDatabase(db):

    """
    Check if database exists. If not, create it.
    """

    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    engine = create_engine("sqlite:///%s" % db)#, echo=True)

    Session = sessionmaker()
    Session.configure(bind=engine)

    session = Session()

    try:

        # Try to open file for reading.
        fp = open(db, 'r')

    except IOError:
        
        if DEBUG:
            
            print "Creating Tables................",
            
            metadata = Base.metadata
            metadata.create_all(engine)
            
            print "Done"
            print "Populating Tables..............",
            
            session = populateTables(session)
            
            print "Done"
            
        else:
            
            metadata = Base.metadata
            metadata.create_all(engine)
            
            session = populateTables(session)
            
        insertDummy(session)
        
    return session

def insertDummy(session):

    b3rx = User('b3rx', 'retardko')
    b3rx.user_type = 1
    
    permission_dict = \
    {
        'view_transaction': True, 'add_transaction': True, 
        'delete_transaction': True, 'view_report': True, 
        'print_report': True, 'view_maintenance': True, 
        'view_collection': True, 'modify_collection': True, 
        'view_userlist': True, 'modify_userlist': True, 
        'delete_userlist': True, 'view_log': True, 'print_log': True, 'clear_log': True
    }
    
    b3rx.set_permissions(permission_dict)
    
    dr3w = User('dr3w', 'test')
    dr3w.user_type = 0
    
    permission_dict = \
    {
        'view_transaction': False, 'add_transaction': True, 
        'delete_transaction': False, 'view_report': True, 
        'print_report': True, 'view_maintenance': False, 
        'view_collection': False, 'modify_collection': False, 
        'view_userlist': False, 'modify_userlist': False, 
        'delete_userlist': False, 'view_log': False, 'print_log': False, 'clear_log': False
    }
    
    dr3w.set_permissions(permission_dict)
    
    ads = User('arjay', 'ads')
    ads.user_type = 1
    
    permission_dict = \
    {
        'view_transaction': True, 'add_transaction': True, 
        'delete_transaction': True, 'view_report': True, 
        'print_report': True, 'view_maintenance': True, 
        'view_collection': True, 'modify_collection': True, 
        'view_userlist': True, 'modify_userlist': True, 
        'delete_userlist': True, 'view_log': True, 'print_log': True, 'clear_log': True
    }
    
    ads.set_permissions(permission_dict)
    
    session.add(b3rx)
    session.add(dr3w)
    session.add(ads)
    session.commit()

def populateTables(session):

    particulars = [
        ["BN Registration", "New", 150],
        ["BN Registration", "Renewal", 150],
        ["BN Registration", "Surcharge Fees", 100],
        ["BN Registration", "Certification Fees", 50],
        ["BN Registration", "Change of Business Name", 50],
        ["BN Registration", "Documentary Stamps", 15],
        ["BN Registration", "Bulk Sales", 55],
        ["Accreditation Fee", "1 Star", 350],
        ["Accreditation Fee", "2 Star", 400],
        ["Accreditation Fee", "3 Star", 425],
        ["Accreditation Fee", "4 Star", 450],
        ["Accreditation Fee", "5 Star", 500],
        ["Accreditation Fee", "Filing Fees", 50],
        ["Accreditation Fee", "Surcharge Fees", 0],
        ["Sales Promo", "One Province", 250],
        ["Sales Promo", "Several Provinces", 500],
        ["Sales Promo", "Two or more Regions", 750],
        ["Real Estate", "Exam Fee", 250],
        ["Real Estate", "Salesman", 200],
        ["Real Estate", "Appraiser", 400],
        ["Real Estate", "Broker", 350],
        ["Real Estate", "Consultant", 500],
        ["Real Estate", "Surcharge Fees", 0],
        ["Fire Extinguisher", "Wholesaler", 1500],
        ["Fire Extinguisher", "Retailer", 150],
        ["Fire Extinguisher", "Salesman", 100],
        ["PETC Fee", "Assessment Fee", 8000],
        ["PETC Fee", "PETC Certification", 3000],
        ["PETC Fee", "Application Fee", 300],
        ["PETC Fee", "Annual Assessment", 1000],
        ["Deposit", "Bank Deposit", 0],
    ]

    # add particulars to the database
    for p in particulars:

        session.add(Particular(p[0], p[1], p[2]))

    session.commit()

    return session

def main():

    session = checkDatabase('colrecsys.sqlite')

    for instance in session.query(Customer):

        instance.display()

    session.close()

if __name__ == '__main__':

    main()

    sys.exit(0)
